"""
通讯录
"""
import MySQLdb

conn = None


def ensure_connection():
    global conn
    if not conn:
        conn = MySQLdb.connect(host='47.104.31.138', port=3306,
                               user='hellokitty', password='Hellokitty.618',
                               database='python2005', charset='utf8mb4')
    return conn


def add_contact():
    """添加联系人"""
    ensure_connection()
    name = input('姓名: ').strip()
    tel = input('电话: ').strip()
    email = input('邮箱: ').strip()
    company = input('公司: ').strip()
    memo = input('备注: ').strip()
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'insert into tb_contact (name, tel, email, company, memo) '
                'values (%s, %s, %s, %s, %s)',
                (name, tel, email, company, memo)
            )
            if affected_rows == 1:
                print('新增联系人成功！')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def show_all_contacts():
    """显示所有联系人"""
    ensure_connection()
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute('select id, name from tb_contact')
        display_contacts(cursor.fetchall())


def display_contacts(all_rows):
    """显示联系人名字清单"""
    ensure_connection()
    for index, row_dict in enumerate(all_rows):
        print(f'[{index + 1}]: {row_dict["name"]}')
    try:
        choice = int(input('输入编号查看联系人详情: '))
        if 0 < choice <= len(all_rows):
            contact_id = all_rows[choice - 1]['id']
            show_contact_detail(contact_id)
    except ValueError:
        pass


def delete_contact(contact_id):
    """删除联系人"""
    ensure_connection()
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'delete from tb_contact where id=%s', (contact_id, )
            )
            if affected_rows == 1:
                print('联系人信息已删除！')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def edit_contact(contact_id, contact_dict):
    """编辑联系人"""
    ensure_connection()
    name = input('姓名: ').strip() or contact_dict['name']
    tel = input('电话: ').strip() or contact_dict['tel']
    email = input('邮箱: ').strip() or contact_dict['email']
    company = input('公司: ').strip() or contact_dict['company']
    memo = input('备注: ').strip() or contact_dict['memo']
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute(
                'update tb_contact set name=%s, tel=%s, email=%s, company=%s, memo=%s '
                'where id=%s',
                (name, tel, email, company, memo, contact_id)
            )
            if affected_rows == 1:
                print('联系人信息已修改！')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()


def show_contact_detail(contact_id):
    """显示联系人的详情"""
    ensure_connection()
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute(
            'select name, tel, email, company, memo from tb_contact where id=%s',
            (contact_id, )
        )
        contact_dict = cursor.fetchone()
        print(f'\n姓名：{contact_dict["name"]}')
        print(f'电话：{contact_dict["tel"]}')
        print(f'邮箱：{contact_dict["email"]}')
        print(f'公司：{contact_dict["company"]}')
        print(f'备注：{contact_dict["memo"]}', end='\n\n')
        print('1. 删除联系人')
        print('2. 编辑联系人')
        try:
            choice = int(input('请选择: '))
            if choice == 1:
                confirm = input(f'确定要删除联系人{contact_dict["name"]}的信息？(yes/no)')
                if confirm.strip().lower() == 'yes':
                    delete_contact(contact_id)
            elif choice == 2:
                edit_contact(contact_id, contact_dict)
        except ValueError:
            pass


def search_contacts():
    """搜索联系人"""
    ensure_connection()
    keyword = input('关键词: ')
    keyword = f'%{keyword}%'
    with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
        cursor.execute(
            'select id, name from tb_contact '
            'where name like %s or company like %s or memo like %s',
            (keyword, keyword, keyword)
        )
        display_contacts(cursor.fetchall())


def list_contacts():
    print('1. 查看所有联系人')
    print('2. 搜索联系人')
    print('3. 返回')
    choice = 0
    while choice <= 0 or choice > 3:
        try:
            choice = int(input('请选择: '))
        except ValueError:
            pass
    if choice == 1:
        show_all_contacts()
    elif choice == 2:
        search_contacts()


def main():
    print('欢迎使用通讯录程序'.center(20, '='))
    while True:
        print('1. 新增联系人')
        print('2. 查看联系人')
        print('3. 退出程序')
        choice = 0
        while choice <= 0 or choice > 3:
            try:
                choice = int(input('请选择: '))
            except ValueError:
                pass
        if choice == 1:
            add_contact()
        elif choice == 2:
            list_contacts()
        else:
            print('程序结束'.center(20, '='))
            break


if __name__ == '__main__':
    main()
